package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.AuditClaim;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 报销申请 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-01-01
 */
public interface AuditClaimMapper extends BaseMapper<AuditClaim> {

    @Select("select id,flow_id,arrive_step,step_num,type,`status`,version_num,created_id from audit_claim where id = ${id} and deleted = 0 order by id desc limit 1")
    public AuditClaim getClaimOneByIdDesc(@Param("id") Long id);



    /**
     * 报销申请数量
     */
    @Select({"<script>",
            "SELECT ",

            // 待处理的最小日期
            "MIN( CASE WHEN ",
            "<when test='adminId!=0'>",
            "check_id = ${adminId} AND ",
            "</when>",
            "<when test='adminId==0'>",
            "claim_status = 0 AND ",
            "</when>",
            "min_date IS NOT NULL AND `status` = 0 AND type = 1 THEN DATE ( min_date ) ELSE NULL END ) AS pending_date,",

            // 待处理的数量
            "COUNT( DISTINCT CASE WHEN ",
            "<when test='adminId!=0'>",
            "check_id = ${adminId} AND",
            "</when>",
            "<when test='adminId==0'>",
            "claim_status = 0 AND ",
            "</when>",
//            " ((`status` = 0 AND type = 1) OR (claim_status = 0 AND type = 0)) THEN sourse_id ELSE NULL END ) AS pending_count,",
            "claim_status = 0 AND `status` = 0 AND type = 1 THEN sourse_id ELSE NULL END ) AS pending_count,",

            // 已处理的最小日期
            "MIN( CASE WHEN ",
            "<when test='adminId!=0'>",
            "check_id = ${adminId} AND ",
            "</when>",
            "<when test='adminId==0'>",
            "claim_status &lt;&gt; 0 AND ",
            "</when>",
            "operate_date_time IS NOT NULL AND `status` &lt;&gt; 0 AND type = 1 THEN DATE ( operate_date_time ) ELSE NULL END ) AS processed_date,",

            // 已处理的数量
            "COUNT( DISTINCT CASE WHEN ",
            "<when test='adminId!=0'>",
            "check_id = ${adminId} AND ",
            "</when>",
            "<when test='adminId==0'>",
            "claim_status &lt;&gt; 0 AND ",
            "</when>",
//            " ((`status` &lt;&gt; 0 AND type = 1) OR (claim_status &lt;&gt; 0 AND type = 0)) THEN sourse_id ELSE NULL END ) AS processed_count,",
            " `status` &lt;&gt; 0 AND type = 1 THEN sourse_id ELSE NULL END ) AS processed_count,",

            // 已发起的最小日期
            "MIN( CASE WHEN ",
            "<when test='adminId!=0'>",
            " check_id = ${adminId} AND ",
            "</when>",
            "operate_date_time IS NOT NULL AND type = 0 THEN DATE(operate_date_time) ELSE NULL END ) AS sponsor_date,",

            // 已发起的数量
            "COUNT( DISTINCT CASE WHEN ",
            "<when test='adminId!=0'>",
            " check_id = ${adminId} AND ",
            "</when>",
            " type = 0 THEN sourse_id ELSE NULL END ) AS sponsor_count,",

            // 我收到的最小日期
            "MIN( CASE WHEN ",
            "<when test='adminId!=0'>",
            " check_id = ${adminId} AND ",
            "</when>",
            "arrive_date_time IS NOT NULL ",
            "AND type = 2 THEN DATE(arrive_date_time) ELSE NULL END ) AS send_date, ",

            // 我收到的数量
            "COUNT( DISTINCT CASE WHEN ",
            "<when test='adminId!=0'>",
            " check_id = ${adminId} AND ",
            "</when>",
            "arrive_date_time IS NOT NULL ",
            "AND type = 2 THEN sourse_id ELSE NULL END ) AS send_count ",

            "FROM (",
            "SELECT",
            "t2.*,t1.`status` AS claim_status,t1.created_at AS min_date  ",
            "FROM",
            "audit_claim t1",
            "LEFT JOIN audit_flow_detail t2 ON t1.id = t2.sourse_id ",
            "WHERE",
            "t1.deleted = 0",
            ") AS t",
            "</script>"})
    public abstract AuditClaim getAuditClaimCount(@Param("adminId") String adminId);

    /**
     * 报销申请列表
     */
    @Select({"<script>",
            "SELECT",
            "t1.*,",
            "t2.`name` AS teacherName,",
            "t3.`name` AS schoolName ",
            "FROM",
            "audit_claim t1",
            "LEFT JOIN staff t2 ON t1.created_id = t2.id AND t2.deleted = 0",
            "LEFT JOIN organization t3 ON t1.school_id = t3.id AND t3.deleted = 0",
            "LEFT JOIN audit_flow_detail t4 ON t1.id = t4.sourse_id ",
            "WHERE",
            "t1.deleted = 0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t1.school_id IN (${schoolId}) ",
            "</when>",
            "<when test='type!=null and type!=&apos;&apos; '>",
                "AND t1.type IN (${type}) ",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos; '>",
                "AND t2.name LIKE '%${name}%' ",
            "</when>",

            "<when test='status!=null and status!=&apos;&apos; '>",

                "<when test='status == 1'>",
                    // 待处理
                    "<when test='adminId!=0'>",
                        "AND t4.check_id = ${adminId}",
                    "</when>",
                    "<when test='adminId==0'>",
                        "AND t1.`status` = 0",
                    "</when>",
//                    "AND ((t4.`status` = 0 AND t4.type = 1) OR (t1.`status` = 0 AND t4.type = 0))",
                    "AND t1.`status` = 0 AND t4.`status` = 0 AND t4.type = 1",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
                        "AND t1.created_at BETWEEN #{startDate} AND #{endDate} ",
                    "</when>",
                "</when>",

                "<when test='status == 2'>",
                    // 已处理
                    "<when test='adminId!=0'>",
                        "AND t4.check_id = ${adminId}",
                    "</when>",
                    "<when test='adminId==0'>",
                        "AND t1.`status` &lt;&gt; 0",
                    "</when>",
//                    "AND ((t4.`status` &lt;&gt; 0 AND t4.type = 1) OR (t1.`status` &lt;&gt; 0 AND t4.type = 0))",
                    "AND t4.`status` &lt;&gt; 0 AND t4.type = 1",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
                        "AND t4.operate_date_time BETWEEN #{startDate} AND #{endDate} ",
                    "</when>",
                "</when>",

                "<when test='status == 3'>",
                    // 已发起
                    "<when test='adminId!=0'>",
                        "AND t4.check_id = ${adminId}",
                    "</when>",
                    "AND t4.type = 0",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
                        "AND t1.created_at BETWEEN #{startDate} AND #{endDate} ",
                    "</when>",
                "</when>",

                "<when test='status == 4'>",
                    // 我收到的
                    "<when test='adminId!=0'>",
                        "AND t4.check_id = ${adminId}",
                    "</when>",
                    "AND t4.type = 2",
                    "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
                        "AND t4.arrive_date_time BETWEEN #{startDate} AND #{endDate} ",
                    "</when>",
                    "AND t4.arrive_date_time IS NOT NULL ",
                "</when>",

            "</when>",

            "GROUP BY t1.id ",
            "</script>"})
    public abstract List<AuditClaim> getAuditClaimList(IPage<AuditClaim> page, @Param("schoolId") String schoolId, @Param("name") String name, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("type") String type, @Param("status") String status, @Param("adminId") String adminId);

    /**
     * 报销申请详情
     */
    @Select({"<script>",
            "SELECT",
            "t1.*,",
            "getStaffNameForId ( t1.apply_id ) AS apply_name,",
            "t2.`name` AS teacherName,",
            "t3.`name` AS schoolName, ",
            "t4.`name` AS departName, ",
            "t5.account_name,",
            "t5.account_no ",
            "FROM",
            "audit_claim t1",
            "LEFT JOIN staff t2 ON t1.created_id = t2.id AND t2.deleted = 0",
            "LEFT JOIN organization t3 ON t1.school_id = t3.id AND t3.deleted = 0",
            "LEFT JOIN department t4 ON t1.depart_id = t4.id AND t4.deleted = 0",
            "LEFT JOIN audit_receivable_account AS t5 ON t1.receivable_account_id = t5.id",
            "WHERE",
            "t1.deleted = 0 ",
            "AND t1.id = #{id} ",
            "</script>"})
    public abstract AuditClaim getAuditClaimDetail(@Param("id") String id);

}
